<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Billing_mod extends CI_Model {

    private $_dbTableBilling = 'billing';
    private $_dbTableUsers = 'users';

    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

    public function getBillingStatuses() {
        $query = $this->db->select(
                        $this->_dbTableBilling . '.status'
                )
                ->from($this->_dbTableBilling)
                ->distinct()
                ->get();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() > 0) {
            $result = $query->result();
            $query->free_result();
        } else {
            $result = NULL;
        }

        return $result;
    }

    public function getBillings($filter = NULL) {

        $where = "";

        if (isset($filter['date_from']) && $filter['date_from'] != "") {
            $where .= "( ";
            $where .= $this->_dbTableBilling . ".date >= '" . $filter['date_from'];
            $where .= "' )";
        }
        if (isset($filter['date_to']) && $filter['date_to'] != "") {
            if ($where != "") {
                $where .= " AND ";
            }
            $where .= "( " . $this->_dbTableBilling . ".date <='" . $filter['date_to'];
            $where .= "' ) ";
        }
        if (isset($filter['status']) && $filter['status'] != "") {
            if ($where != "") {
                $where .= " AND ";
            }
            $where .= "( " . $this->_dbTableBilling . ".status LIKE '" . $filter['status'];
            $where .= "') ";
        }
        if (isset($filter['transaction_id']) && $filter['transaction_id'] != "") {
            if ($where != "") {
                $where .= " AND ";
            }
            $where .= "( " . $this->_dbTableBilling . ".transaction_id LIKE" . $filter['transaction_id'];
            $where .= " ) ";
        }
        if (isset($filter['user_id']) && $filter['user_id'] != "") {
            if ($where != "") {
                $where .= " AND ";
            }
            $where .= "( " . $this->_dbTableBilling . ".user_id LIKE" . $filter['user_id'];
            $where .= " ) ";
        }
        $this->db->select
                ("transaction_id, user_id, date, status, provider, ip_address, amount, error, extra, "
                . " (SELECT username FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`id` = `user_id`) as username,"
                . " (SELECT email FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`id` = `user_id`) as email,"
                . " (SELECT coins FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`id` = `user_id`) as coins"
        );
        $this->db->from($this->_dbTableBilling);

        if ($where != "")
            $this->db->where($where);

        $this->db->order_by('date', 'desc');

        $query = $this->db->get();

        //echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() > 0) {
            $result = $query->result();
            $query->free_result();
        } else {
            $result = NULL;
        }

        return $result;
    }

    public function getTotal($period = NULL) {
        $where = "";
        switch ($period) {
            case "month":
                $where .= "( ";
                $where .= $this->_dbTableBilling . ".date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)";
                $where .= ")";
                break;
            case "week": 
                    $where .= "( ";
                    $where .= $this->_dbTableBilling . ".date >= DATE_SUB(NOW(), INTERVAL 1 WEEK)";
                    $where .= ")";
                    break;
                
            case "day":
            default : 
                    $where .= "( ";
                    $where .= $this->_dbTableBilling . ".date >= DATE_SUB(NOW(), INTERVAL 1 DAY)";
                    $where .= " )";
                    break;
                
        }
        
                $this->db->select_sum(
                        $this->_dbTableBilling . '.amount'
                );
                $this->db->from($this->_dbTableBilling);
                if ($where != "")
                $this->db->where($where);
                $query = $this->db->get();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() > 0) {
            $result = $query->result();
            $query->free_result();
        } else {
            $result = NULL;
        }
        
        return $result[0];
        
    }

}

?>
